package com.health.system.process.util;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.DependsOn;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@DependsOn("DBPoolConnection")
@Component
public class DBUtil {

	@Autowired
	DBPoolConnection dbPoolConnection;

	Connection con = null;

	static Logger logger = LoggerFactory.getLogger(DBUtil.class);

	@PostConstruct
	void init() throws SQLException {
		con = dbPoolConnection.getConnection();
	}

	public ResultSet execute(String sql) throws SQLException {
		assert con != null;
		if(con.isClosed())
			init();
		Statement statement = con.createStatement();
		ResultSet rs = statement.executeQuery(sql);
		return rs;
	}
	public boolean extractToExcel(String sql,String path,String fileName){
		try {
			ResultSet resultSet = execute(sql);
			ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
			List<List<Object>> ret = new ArrayList<>();
			List<Object> header = new ArrayList<>();
			int colCount = resultSetMetaData.getColumnCount();
			for(int i=1;i<=colCount;i++)
				header.add(resultSetMetaData.getColumnName(i));
			ret.add(header);
			while (resultSet.next()){
				List<Object> row = new ArrayList<>();
				for(int i=1;i<=colCount;i++)
					row.add(resultSet.getObject(i));
				ret.add(row);
			}
			DataToExcel.ToExcel(ret,path,fileName);
		} catch (SQLException throwables) {
			throwables.printStackTrace();
			return false;
		}
		return true;
	}
}